
--select * from fn_backlog_hold_for_vertical_report (0,'0','2','2005','Hold','0','Requested','05/18/2005','OPERATIONS',6131)
	
alter  function fn_backlog_hold_for_vertical_report(
	
	 
	
@product_Id int,
@customer_Id varchar(20),
@quarter varchar(10),
@year  varchar(10),
@Backlog_Type varchar(20),
@defaultPrompt varchar(50),
@category varchar(15),
@CURRENT_DATE datetime,
@Hold_Group varchar(30),
@LINEITEMID INT )
	
returns @fn_results Table(Amount float, status varchar(20), orderDate datetime) 

as
begin
	 
	declare @results table(Amount float, status varchar(20), orderDate datetime) 
	 
	 declare @part_Number varchar(50)
	 declare @customer_Name varchar(65)
	 declare @schedule_Date datetime
	 declare @amount float
	 declare @status varchar(50)
	 declare @date_From varchar(10)
	 declare @date_To varchar(10)
	 declare @dateDiff datetime
	
	 declare @month varchar(10)
	 declare @sales_rep varchar(50)
	 declare @end_user varchar(50)
	
	 declare @LineItem_ID int
	 declare @Unit_Price float 
	 declare @Shipped_Qty int
	 declare @Shipped_Amount float
	  
	 declare @revised_date datetime 
	 declare @requested_date datetime 
	
	 declare @req_date datetime
	
	 if @Backlog_Type = 'all'
	 begin
		set @backlog_Type = '0'
	 end
	 
	 set @month = '1' 
	
	 set @date_From = CONVERT(varchar,CONVERT(datetime,@month+'/01/'+@year),101)
	 set @date_To   = CONVERT(varchar,DATEADD(month,12,CONVERT(datetime,@date_From)),101)
	 set @dateDiff  = CONVERT(datetime,DATEDIFF(day,1,@date_To),101)
	 set @date_To   = CONVERT(varchar,@dateDiff,101)
	
	 declare @CoolCursor CURSOR
	
		if (@category = 'REQUESTED')                
		begin
			if( @Hold_Group = '')			
			begin
				set @CoolCursor = CURSOR for
				select product.Part_Number,
				customer.C_Name,COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Rep, Sales_Order.SO_EndUser
				from product, Customer_Open_Order_LineItem COOL, Sales_Order,Customer 
				where 
				COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.Customer_ID=customer.Customer_ID
				and Sales_Order.SO_Is_Sample = 1
				and COOL_Status <> 'SHIPPED'
				and ((COOL.COOL_Requested_Date>=@date_From and COOL.COOL_Requested_Date<=@date_To)
				or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
				and COOL.LineItem_Id = @LINEITEMID
			end
			else
			begin	
				set @CoolCursor = CURSOR for
				select product.Part_Number,
				customer.C_Name,COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , COOL.LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Rep, Sales_Order.SO_EndUser
				from product, Customer_Open_Order_LineItem COOL, Sales_Order,Customer,sales_hold_table SHT 
				where
				COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.Customer_ID=customer.Customer_ID
				and Sales_Order.SO_Is_Sample = 1
				and COOL_Status <> 'SHIPPED'
				and ((COOL.COOL_Requested_Date>=@date_From and COOL.COOL_Requested_Date<=@date_To)
				or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
				and COOL.LineItem_ID = SHT.LineItem_ID AND COOL.SalesOrder_ID=SHT.SalesOrder_ID 
				and SHT.Hold_By_Group = @Hold_Group and SHT.Is_Approved = 1
				and COOL.LineItem_Id = @LINEITEMID
			end
		end
		else if (@category = 'SCHEDULE')
		begin
			if( @Hold_Group = '')			
			begin
				set @CoolCursor = CURSOR for
				select product.Part_Number,
				customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , COOL.LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Rep, Sales_Order.SO_EndUser
				from product,Customer_Open_Order_LineItem COOL,Sales_Order,Customer 
				where 
				COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.Customer_ID=customer.Customer_ID
				and Sales_Order.SO_Is_Sample = 1
		                and COOL_Status <> 'SHIPPED'
				and ((COOL.COOL_Schedule_Date>=@date_From and COOL.COOL_Schedule_Date<=@date_To)
				or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
				and COOL.LineItem_Id = @LINEITEMID
			end
			else
			begin
				set @CoolCursor = CURSOR for
				select product.Part_Number,
				customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , COOL.LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Rep, Sales_Order.SO_EndUser
				from product,Customer_Open_Order_LineItem COOL,Sales_Order,Customer,sales_hold_table SHT 
				where 
				COOL.Product_ID=product.Product_ID 
				and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
				and Sales_Order.Customer_ID=customer.Customer_ID
				and Sales_Order.SO_Is_Sample = 1
		                and COOL_Status <> 'SHIPPED'
				and ((COOL.COOL_Schedule_Date>=@date_From and COOL.COOL_Schedule_Date<=@date_To)
				or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
				and COOL.LineItem_ID = SHT.LineItem_ID AND COOL.SalesOrder_ID=SHT.SalesOrder_ID 
				and SHT.Hold_By_Group = @Hold_Group and SHT.Is_Approved = 1
				and COOL.LineItem_Id = @LINEITEMID
			end
		end
		
	open @CoolCursor 
	
		fetch next from @CoolCursor
		into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user
	
	while @@fetch_status = 0
	begin
		    
		   if(@amount is null)
		   begin
		    set @amount = 0
		   end
		   if (@category = 'REQUESTED')                
        		begin
				
				if( @schedule_Date < @CURRENT_DATE )
				begin
					set @schedule_Date = @CURRENT_DATE
				end
				else
				begin
					set @schedule_Date = @schedule_Date
				end
			end	
	
			if (@category = 'SCHEDULE')
			begin
				if(@schedule_Date is null OR @schedule_Date= '')
				begin
					
					set @schedule_Date = @CURRENT_DATE
				end
				else
				begin
					set @schedule_Date = @schedule_Date

					if( @schedule_Date < @CURRENT_DATE )
					begin
						set @schedule_Date = @CURRENT_DATE
					end
					else
					begin
						set @schedule_Date = @schedule_Date
					end
				end
			end
			
			if (@category = 'SCHEDULE')
			begin
				if(@revised_date is not null OR @revised_date <> '')
				begin
					set @schedule_Date = @revised_date
					
					if( @schedule_Date < @CURRENT_DATE )
					begin
						set @schedule_Date = @CURRENT_DATE
					end
					else
					begin
						set @schedule_Date = @schedule_Date
					end

					
				end
			end

		
		   select @Shipped_Qty = isNull(sum(CSLIT_Qty_Shipped),0) from Customer_Shipping_Line_Item_Table where LineItem_ID = @LineItem_ID and CSLIT_Status = 'SHIPPED'
		   
		   set @amount = @amount - (@Unit_Price * @Shipped_Qty) 
		
		 
		
		   if ( @amount < 0 )
		   begin
		    set @amount = 0 
		   end
		
		   insert into @results(Amount, status, orderDate ) values( @amount, @status, @schedule_Date )  

		   set @part_number = null
		   set @customer_name = null
		   set @schedule_date = null
		   set @amount = null
		   set @status = null
		   set @LineItem_ID = null
		   set @unit_price = null
		   set @requested_date = null
		   set @revised_date = null
		
		fetch next from @CoolCursor
		into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user
		
	end

	 close @CoolCursor
	 deallocate @CoolCursor
		
		 
		if @Backlog_Type <> '0' 
			begin
				
				if (@Category = 'REQUESTED')
				begin
					insert @fn_results select * from @results where status = @Backlog_Type --order by Req_Date
					return
				end
				else if (@Category = 'SCHEDULE')
				begin
					insert @fn_results select * from @results where status = @Backlog_Type --order by Schd_Date
					return
				end
			end
			else
			begin
				
				
				if (@Category = 'REQUESTED')
				begin
					insert @fn_results select * from @results --order by Req_Date
					return
				end
				else if (@Category = 'SCHEDULE')
				begin
					insert @fn_results select * from @results --order by Schd_Date
					return
				end
			end
	
return 
end
	
	
	
